package db;

import java.io.IOException;
import javax.servlet.GenericServlet;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;


import java.sql.*;

import ulti.tuple.Pair;;

/**
 * Servlet implementation class DatabaseServe5
 */
@WebServlet(
		description = "连接数据库，保存在application中", 
		urlPatterns = { "/DatabaseServlet" },
		loadOnStartup=1,
		initParams= {
				@WebInitParam(name="driver",value="com.mysql.cj.jdbc.Driver"),
				@WebInitParam(name="url",value="jdbc:mysql://localhost:3306/sprite?useSSL=false&serverTimezone=UTC&autoReconnect=true"),
				@WebInitParam(name="user",value="root"),
				@WebInitParam(name="password",value="mysqlDLnetwork.")
				}
)


public class DataBaseServlet extends GenericServlet {
	private static final long serialVersionUID = 1L;
	
	/**
	 * 数据库单例
	 */
	static public DataBaseServlet DB = null;
       
    /**
     * @see GenericServlet#GenericServlet()
     */
    public DataBaseServlet() {
        super();
        // TODO Auto-generated constructor stub
        DB = this;
    }

    private Connection connection = null;
    
    /*为true表示正常工作，为false表示连接断开*/
    private boolean active = false;
    
    
	/**
	 * @see Servlet#init(ServletConfig)
	 * 初始化连接数据库
	 */
	public void init(ServletConfig config) throws ServletException {
		//获取ServletConfig对象
        //ServletConfig cfg = getServletConfig();
        //根据参数名获取参数值
		String driver = "";
        String url = "";
        String user = "";
        String password = "";
		
		try {
	        driver = config.getInitParameter("driver");
	        url = config.getInitParameter("url");
	        user = config.getInitParameter("user");
	        password = config.getInitParameter("password");
        }
		catch(NullPointerException e){
            // 处理 JDBC 错误
			System.out.println("数据库初始参数错误");
            e.printStackTrace();
            return;
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
            return;
        }
        
        boolean suc = false;
        //开始连接
        try{
            // 注册 JDBC 驱动
            Class.forName(driver);
            // 打开链接
            System.out.println("准备连接数据库...");
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("连接成功");
            suc = true;
        }
        catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }
        catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
        active = suc;
	}

	/**
	 * @see Servlet#service(ServletRequest request, ServletResponse response)
	 */
	public void service(ServletRequest request, ServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		if(! active) {
			System.out.println("尝试连接数据库");
		}
	}
	
	public void destroy() {
		try{
			if(connection!= null) {
	            connection.close();
	            System.out.println("关闭数据库!");
			}
    	}
    	catch(SQLException e){
            // 处理 JDBC 错误
            e.printStackTrace();
        }
        catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
	}
	
	/**
     * 执行查询
     * @param sql 查询语句
     */
	public ResultSet sqlQuery(String sql) {
	    try{
	    	 // 查询对象
			Statement stmt = connection.createStatement();
	    	ResultSet rs = stmt.executeQuery(sql);
	    	//stmt.close();
	    	return rs;
	    	
	    }
		catch(SQLException se){
		    // 处理 JDBC 错误
		    se.printStackTrace();
		}
		catch(Exception e){
		    // 处理 Class.forName 错误
		    e.printStackTrace();
		}
	    return null;
	}
	
	/**
     * 执行更新
     * @param sql 更新语句
     */
	public int sqlUpdate(String sql) {
		try{
	    	 // 查询对象
			Statement stmt = connection.createStatement();
	    	int res = stmt.executeUpdate(sql);
	    	stmt.close();
	    	return res;
	    	
	    }
		catch(SQLException se){
		    // 处理 JDBC 错误
		    se.printStackTrace();
		}
		catch(Exception e){
		    // 处理 Class.forName 错误
		    e.printStackTrace();
		}
	    return 0;
		
	}
	
	/**
	 * 新建查询
	 * @return 查询状态
	 */
	public Statement newQuery() {
		Statement stmt = null;
		try{
	    	 // 查询对象
			stmt = connection.createStatement();
	    }
		catch(SQLException se){
		    // 处理 JDBC 错误
		    se.printStackTrace();
		}
		catch(Exception e){
		    // 处理 Class.forName 错误
		    e.printStackTrace();
		}
		return stmt;
	}
	
	/**
	 * 查询设备信息
	 * @param deviceId 设备Id
	 * @return json对象字符串 || null
	 */
	public String queryDeviceInfo(int deviceId){
		final String SQL_BASE = "select online, power from device where device.id = %d;";
		final String SQL_DETAIL = "SELECT attribute.name, attribute.art, device_attribute.attributeId, device_attribute.value"
				+ " from device,attribute,device_attribute"
				+ " where device.id = %d and device_attribute.attributeId = attribute.id;";
		
		final String DEFAULT = "{\"suc\":false}";
		
		// 创建查询对象
		Statement stmt = newQuery();
		if(stmt == null) {
			return DEFAULT;
		}
		
		StringBuffer sBuffer = new StringBuffer(); //返回结果缓冲
		
		//region 查询设备信息
		String sql = null;
		ResultSet res = null;
		
		//	基础信息
		sql = String.format(SQL_BASE, deviceId);
		Boolean power = false;
		try {
			res = stmt.executeQuery(sql);
			StringBuffer buffer = new StringBuffer();
			if (res.first()){
				buffer .append("\"online\":");
				buffer .append(res.getBoolean("online"));
				buffer .append(",");
				buffer .append("\"power\":");
				power = res.getBoolean("power");
				buffer .append(power);
			}
			res.close();
			sBuffer.append(buffer.toString());
			sBuffer.append(",\"suc\": true");
			
		}
		catch (SQLException e) {
			e.printStackTrace();
			sBuffer.append("\"suc\": false");
		}
		
		// 详细信息
		if(power) {
			// 设备启动, 详细信息查询

			sql = String.format(SQL_DETAIL, deviceId);
			try {
				res = stmt.executeQuery(sql);
				StringBuffer buffer = new StringBuffer();
				buffer.append("[");
				while (res.next()){
					buffer.append("{");
					buffer.append("\"id\":\"");
					buffer.append(res.getInt("attributeId"));
					buffer.append("\",");
					buffer.append("\"name\":\"");
					buffer.append(res.getString("name"));
					buffer.append("\",");
					buffer.append("\"value\":\"");
					buffer.append(res.getString("value"));
					buffer.append("\",");
					buffer.append("\"art\":\"");
					buffer.append(res.getString("art"));
					buffer.append("\"},");
				
				}
				buffer.deleteCharAt(buffer.length() - 1);
				buffer.append("]");
				res.close();
				sBuffer.append(",\"detail\":");
				sBuffer.append(buffer.toString());
				
			} 
			catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//endregion
		
		
		sBuffer.insert(0, '{');
		sBuffer.insert(sBuffer.length(), '}');
		return sBuffer.toString();
	}
	
	/**
	 * 查询用户最近会话设备列表
	 */
	public String queryUserDeviceList() {
		final String DEFAULT = "{\"suc\":false}";
		final String SQL = "SELECT id,  name, online, power, art FROM device";
		
		// 创建查询对象
		Statement stmt = newQuery();
		if(stmt == null) {
			return DEFAULT;
		}
		
		//返回结果缓冲
		StringBuffer sBuffer = new StringBuffer(); 
		
		//region 查询设备信息
		ResultSet res = null;
		String sql = String.format(SQL);
		try {
			res = stmt.executeQuery(sql);
			while (res.next()){
				
				sBuffer.append("{\"deviceId\":");
				sBuffer.append(res.getInt("id"));
				sBuffer.append(",\"name\":\"");
				sBuffer.append(res.getString("name"));
				sBuffer.append("\",\"online\":");
				sBuffer.append(res.getBoolean("online"));
				sBuffer.append(",\"power\":");
				sBuffer.append(res.getBoolean("power"));
				sBuffer.append(",\"art\":\"");
				sBuffer.append(res.getString("art"));
				sBuffer.append("\"");
				sBuffer.append("},");
			
			}
			sBuffer.deleteCharAt(sBuffer.length() - 1);
			sBuffer.insert(0, "{\"suc\":true,\"device\":[");
			sBuffer.append("]}");
			res.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
			return DEFAULT;
		}
		// endregion
		return sBuffer.toString();
	}
	
	/**
	 * 反转设备电源状态
	 * @param deviceId 设备Id
	 * @return Pair<Boolean, Boolean> 1.操作是否成功 2.当前设备电源状态
	 */
	public Pair<Boolean, Boolean> switchDevice(int deviceId) {
		final String SQL_UPDATE = "update device set power = not power where id = %d;";
		final String SQL_QUERY = "select power from device where id = %d;";
		
		Pair<Boolean,  Boolean> ret = new Pair<>(false, false);
		// 创建查询对象
		Statement stmt = newQuery();
		if(stmt == null) {
			return ret;
		}
		
		//	基础信息
		try {
			String sql = String.format(SQL_UPDATE, deviceId);
			if (stmt.executeUpdate(sql) == 0) {
				throw new SQLException();
			}
			sql = String.format(SQL_QUERY, deviceId);
			ResultSet res = stmt.executeQuery(sql);
			if (res.first()){
				ret.second = res.getBoolean("power");
			}
			res.close();
			ret.first = true;
			
		}
		catch (SQLException e) {
			e.printStackTrace();
			return ret;
		}
		
		return ret;
	}
	
	/**
	 * 查询设备电源状态
	 * @return Pair<Boolean, Boolean> 1.操作是否成功 2.当前设备电源状态
	 */
	public Pair<Boolean,  Boolean> queryPower(int deviceId) {
		final String SQL_QUERY = "select power from device where id = %d;";
		Pair<Boolean,  Boolean> ret = new Pair<>(false, false);
		
		// 创建查询对象
		Statement stmt = newQuery();
		if(stmt == null) {
			return ret;
		}
		
		//	基础信息
		try {
			String sql = String.format(SQL_QUERY, deviceId);
			ResultSet res = stmt.executeQuery(sql);
			if (res.first()){
				ret.second = res.getBoolean("power");
			}
			res.close();
			ret.first = true;
			
		}
		catch (SQLException e) {
			e.printStackTrace();
			return ret;
		}
		
		return ret;
	}

}
